<?php


namespace PKFrame\DataHandler;
defined('PATH_PK') or die();

require_once PATH_PK . 'PlugIn' . DS . 'excel' . DS . 'PHPExcel.php';

class Excel
{

    protected $file_excel;
    protected $allColumn;
    protected $count_row;
    protected $read_table_index = 0;

    public function __construct($file_excel = null)
    {
        if (!empty($file_excel)) {
            try {
                if (!file_exists($file_excel)) {
                    throw new \Exception('no Exists Of Excel file, path:' . $file_excel);
                }
                $this->file_excel = $file_excel;
            } catch (\Exception $e) {
                handlerException($e);
            }
        }
    }

    protected function excelCls()
    {
        static $excel_cls = '';
        $file_ext = fileHelper()->GetFileExtension($this->file_excel);
        if (empty($excel_cls)) {
            try {
                switch ($file_ext) {
                    case 'xls':
                        $excel_cls = new \PHPExcel_Reader_Excel5();
                        break;
                    case 'xlsx':
                        //建立reader对象
                        $excel_cls = new \PHPExcel_Reader_Excel2007();
                        break;
                    default:
                        throw new \Exception('Not an Excel file!');
                }
            } catch (\Exception $e) {
                handlerException($e);
            }
        }
        return $excel_cls;
    }

    protected function currentSheet(): ?\PHPExcel_Worksheet
    {
        static $currentSheet = null;
        if (empty($currentSheet)) {
            try {
                if (!$this->excelCls()->canRead($this->file_excel)) {
                    throw new \Exception('no Excel!');
                }
                //建立excel对象
                $excel = $this->excelCls()->load($this->file_excel);
                //**读取excel文件中的指定工作表*/
                $currentSheet = $excel->getSheet($this->read_table_index);
            } catch (\PHPExcel_Reader_Exception $e) {
                handlerException($e);
            } catch (\Exception $exception) {
                handlerException($exception);
            }
        }
        return $currentSheet;
    }

    public function ReadExcel()
    {
        //**取得最大的列号*/
        $this->allColumn = $this->currentSheet()->getHighestColumn();
        //**取得一共有多少行*/
        $this->count_row = $this->currentSheet()->getHighestRow();
    }

    public function GetTableHead(): array
    {
        $data = [];
        $row = 1;
        $i = 0;
        // 取出excel第一行全部字段
        while (($column_no = $this->stringFromColumnIndex($i)) != $this->allColumn) {
            $cell = $this->GetCellValueByString($column_no, $row);
            $data[] = ['name' => $cell, 'value' => $column_no,];
            $i++;
        }
        return $data;
    }

    public function stringFromColumnIndex($pColumnIndex = 0): string
    {
        static $_indexCache = [];
        if (!isset($_indexCache[$pColumnIndex])) {
            if ($pColumnIndex < 26) {
                $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
            } elseif ($pColumnIndex < 702) {
                $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26);
            } else {
                $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26);
            }
        }
        return $_indexCache[$pColumnIndex];
    }

    /**
     * 返回总行数
     * @return mixed
     */
    public function GetCountRow()
    {
        return $this->count_row;
    }

    /**
     * 返回总列数
     * @return mixed
     */
    public function GetAllColumn()
    {
        return $this->allColumn;
    }

    /**
     * 读取excel文件中的指定工作表
     * @param int $read_table_index
     */
    public function SetReadTableIndex(int $read_table_index): void
    {
        $this->read_table_index = $read_table_index;
    }

    protected function getCell(string $column_no, int $row): ?\PHPExcel_Cell
    {
        try {
            return $this->currentSheet()->getCell($column_no . $row);
        } catch (\PHPExcel_Exception $e) {
            handlerException($e);
        }
        return null;
    }

    public function GetCellValueByString(string $column_no, int $row): string
    {
        $cell = (string)$this->getCell($column_no, $row)->getValue();
        //富文本转换字符串
        if ($cell instanceof \PHPExcel_RichText) {
            $cell = $cell->__toString();
        }
        return Auth::FilterValue($cell);
    }

    function GetCellValueOfDateTime(string $column_no, int $row, $date_format = "Y-m-d H:i:s")
    {
        $cell = $this->getCell($column_no, $row);
        $value = $cell->getValue();
        if ($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC) {
            //版本过低的话请加上 getParent 例：$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat();
            $cell_style_format = $cell->getStyle()->getNumberFormat(); //不需要getParent
            $format_code = $cell_style_format->getFormatCode();
            if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                $value = gmdate($date_format, \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
            } else {
                $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
            }
        }
        return $value;
    }

    public function WriterExcel(): \PHPExcel
    {
        return new \PHPExcel();
    }

    public function WriterExcelOfXls($cls_excel): \PHPExcel_Writer_Excel5
    {
        return new \PHPExcel_Writer_Excel5($cls_excel);
    }

    public function WriterExcelOfXlsx($cls_excel): \PHPExcel_Writer_Excel2007
    {
        return new \PHPExcel_Writer_Excel2007($cls_excel);
    }

}